﻿/*
Скрипт развертывания для routelist
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER OFF;


GO
:setvar Path1 "c:\Program Files\Microsoft SQL Server\MSSQL10.$(DatabaseName)\MSSQL\DATA\"
:setvar Path2 "c:\Program Files\Microsoft SQL Server\MSSQL10.$(DatabaseName)\MSSQL\DATA\"
:setvar DatabaseName "routelist"
:setvar DefaultDataPath "c:\Program Files\Microsoft SQL Server\MSSQL10.ROUTELIST\MSSQL\DATA\"
:setvar DefaultLogPath "c:\Program Files\Microsoft SQL Server\MSSQL10.ROUTELIST\MSSQL\DATA\"

GO
:on error exit
GO
USE [master]
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'Для состояния конечной базы данных %s не задано значение ONLINE. Чтобы выполнить развертывание в эту базу данных, необходимо задать для нее состояние ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO

IF NOT EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)')
BEGIN
    RAISERROR(N'Не удается развернуть этот скрипт обновления на конечном объекте BARANTSEV\ROUTELIST. База данных routelist, для которой создан этот скрипт, не существует на данном сервере.', 16, 127) WITH NOWAIT
    RETURN
END

GO

IF CAST(DATABASEPROPERTY(N'$(DatabaseName)','IsReadOnly') as bit) = 1
BEGIN
    RAISERROR(N'Не удается развернуть этот скрипт обновления, так как для базы данных %s, для которой он создан, задано состояние READ_ONLY.', 16, 127, N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
USE [$(DatabaseName)]
GO
/*
 Шаблон скрипта после развертывания							
--------------------------------------------------------------------------------------
 В данном файле содержатся операторы SQL, которые будут исполнены перед скриптом построения.	
 Используйте синтаксис SQLCMD для включения файла в скрипт перед развертыванием.			
 Пример:      :r .\myfile.sql								
 Используйте синтаксис SQLCMD для создания ссылки на переменную в скрипте перед развертыванием.		
 Пример:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO
PRINT N'Выполняется удаление ForeignKeyConstraintRouteListDriver...';


GO
ALTER TABLE [dbo].[RouteList] DROP CONSTRAINT [ForeignKeyConstraintRouteListDriver];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintRouteListRouteTable...';


GO
ALTER TABLE [dbo].[RouteList] DROP CONSTRAINT [ForeignKeyConstraintRouteListRouteTable];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintRouteListVehicle...';


GO
ALTER TABLE [dbo].[RouteList] DROP CONSTRAINT [ForeignKeyConstraintRouteListVehicle];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintRouteListPersonDispetcher...';


GO
ALTER TABLE [dbo].[RouteList] DROP CONSTRAINT [ForeignKeyConstraintRouteListPersonDispetcher];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintRouteListPersonMechanic...';


GO
ALTER TABLE [dbo].[RouteList] DROP CONSTRAINT [ForeignKeyConstraintRouteListPersonMechanic];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintVokzalTableRouteList...';


GO
ALTER TABLE [dbo].[VokzalTable] DROP CONSTRAINT [ForeignKeyConstraintVokzalTableRouteList];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintRouteListInfoRouteList...';


GO
ALTER TABLE [dbo].[RouteListInfo] DROP CONSTRAINT [ForeignKeyConstraintRouteListInfoRouteList];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintRouteListScheduleRouteList...';


GO
ALTER TABLE [dbo].[RouteListSchedule] DROP CONSTRAINT [ForeignKeyConstraintRouteListScheduleRouteList];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintCashRouteList...';


GO
ALTER TABLE [dbo].[Cash] DROP CONSTRAINT [ForeignKeyConstraintCashRouteList];


GO
PRINT N'Выполняется удаление ForeignKeyConstraintRoutePlanRouteTable...';


GO
ALTER TABLE [dbo].[RoutePlan] DROP CONSTRAINT [ForeignKeyConstraintRoutePlanRouteTable];


GO
PRINT N'Выполняется запуск перестройки таблицы [dbo].[RouteList]...';


GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;


GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;


GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRANSACTION;

CREATE TABLE [dbo].[tmp_ms_xx_RouteList] (
    [id]                INT            IDENTITY (1, 1) NOT NULL,
    [nomdog]            NVARCHAR (MAX) NOT NULL,
    [datedoc]           NVARCHAR (MAX) NOT NULL,
    [d_datedoc]         NVARCHAR (MAX) NULL,
    [id_RouteTable]     INT            NULL,
    [departure]         NVARCHAR (MAX) NULL,
    [arrive]            NVARCHAR (MAX) NULL,
    [total_length]      REAL           NULL,
    [total_free_length] REAL           NULL,
    [total_reis_plan]   REAL           NULL,
    [total_reis_fact]   REAL           NULL,
    [total_work_time]   REAL           NULL,
    [total_rest_time]   REAL           NULL,
    [sum_plan]          REAL           NULL,
    [sum_fact]          REAL           NULL,
    [total_workt_fact]  REAL           NULL,
    [total_restt_fact]  REAL           NULL,
    [id_driver]         INT            NULL,
    [id_vehicle]        INT            NULL,
    [id_mechanic]       INT            NULL,
    [id_dispetcher]     INT            NULL,
    [total_length_fact] REAL           NULL,
    [total_free_fact]   REAL           NULL,
    [nomdeparture]      INT            NULL,
    PRIMARY KEY CLUSTERED ([id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[RouteList])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_RouteList] ON;
        INSERT INTO [dbo].[tmp_ms_xx_RouteList] ([id], [nomdog], [datedoc], [id_RouteTable], [departure], [arrive], [total_length], [total_free_length], [total_reis_plan], [total_reis_fact], [total_work_time], [total_rest_time], [sum_plan], [sum_fact], [total_workt_fact], [total_restt_fact], [id_driver], [id_vehicle], [id_mechanic], [id_dispetcher], [total_length_fact], [total_free_fact], [nomdeparture])
        SELECT   [id],
                 [nomdog],
                 [datedoc],
                 [id_RouteTable],
                 [departure],
                 [arrive],
                 [total_length],
                 [total_free_length],
                 [total_reis_plan],
                 [total_reis_fact],
                 [total_work_time],
                 [total_rest_time],
                 [sum_plan],
                 [sum_fact],
                 [total_workt_fact],
                 [total_restt_fact],
                 [id_driver],
                 [id_vehicle],
                 [id_mechanic],
                 [id_dispetcher],
                 [total_length_fact],
                 [total_free_fact],
                 [nomdeparture]
        FROM     [dbo].[RouteList]
        ORDER BY [id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_RouteList] OFF;
    END

DROP TABLE [dbo].[RouteList];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_RouteList]', N'RouteList';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;


GO
SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;


GO
PRINT N'Выполняется запуск перестройки таблицы [dbo].[RoutePlan]...';


GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRANSACTION;

CREATE TABLE [dbo].[tmp_ms_xx_RoutePlan] (
    [id]            INT  IDENTITY (1, 1) NOT NULL,
    [id_routetable] INT  NOT NULL,
    [id_route]      INT  NULL,
    [monday]        REAL NOT NULL,
    [tuesday]       REAL NOT NULL,
    [wednesday]     REAL NOT NULL,
    [thursday]      REAL NOT NULL,
    [friday]        REAL NOT NULL,
    [saturday]      REAL NOT NULL,
    [sunday]        REAL NOT NULL
);

ALTER TABLE [dbo].[tmp_ms_xx_RoutePlan]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PrimaryKeyConstraintRoutePlan] PRIMARY KEY CLUSTERED ([id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[RoutePlan])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_RoutePlan] ON;
        INSERT INTO [dbo].[tmp_ms_xx_RoutePlan] ([id], [id_routetable], [monday], [tuesday], [wednesday], [thursday], [friday], [saturday], [sunday])
        SELECT   [id],
                 [id_routetable],
                 [monday],
                 [tuesday],
                 [wednesday],
                 [thursday],
                 [friday],
                 [saturday],
                 [sunday]
        FROM     [dbo].[RoutePlan]
        ORDER BY [id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_RoutePlan] OFF;
    END

DROP TABLE [dbo].[RoutePlan];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_RoutePlan]', N'RoutePlan';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_clusteredindex_PrimaryKeyConstraintRoutePlan]', N'PrimaryKeyConstraintRoutePlan', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRouteListDriver...';


GO
ALTER TABLE [dbo].[RouteList] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRouteListDriver] FOREIGN KEY ([id_driver]) REFERENCES [dbo].[driver] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRouteListRouteTable...';


GO
ALTER TABLE [dbo].[RouteList] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRouteListRouteTable] FOREIGN KEY ([id_RouteTable]) REFERENCES [dbo].[RouteTable] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRouteListVehicle...';


GO
ALTER TABLE [dbo].[RouteList] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRouteListVehicle] FOREIGN KEY ([id_vehicle]) REFERENCES [dbo].[vehicle] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRouteListPersonDispetcher...';


GO
ALTER TABLE [dbo].[RouteList] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRouteListPersonDispetcher] FOREIGN KEY ([id_dispetcher]) REFERENCES [dbo].[person] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRouteListPersonMechanic...';


GO
ALTER TABLE [dbo].[RouteList] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRouteListPersonMechanic] FOREIGN KEY ([id_mechanic]) REFERENCES [dbo].[person] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintVokzalTableRouteList...';


GO
ALTER TABLE [dbo].[VokzalTable] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintVokzalTableRouteList] FOREIGN KEY ([id_routelist]) REFERENCES [dbo].[RouteList] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRouteListInfoRouteList...';


GO
ALTER TABLE [dbo].[RouteListInfo] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRouteListInfoRouteList] FOREIGN KEY ([id_rl]) REFERENCES [dbo].[RouteList] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRouteListScheduleRouteList...';


GO
ALTER TABLE [dbo].[RouteListSchedule] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRouteListScheduleRouteList] FOREIGN KEY ([id_rl]) REFERENCES [dbo].[RouteList] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintCashRouteList...';


GO
ALTER TABLE [dbo].[Cash] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintCashRouteList] FOREIGN KEY ([id_RouteList]) REFERENCES [dbo].[RouteList] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRoutePlanRouteTable...';


GO
ALTER TABLE [dbo].[RoutePlan] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRoutePlanRouteTable] FOREIGN KEY ([id_routetable]) REFERENCES [dbo].[RouteTable] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Выполняется создание ForeignKeyConstraintRoutePlanRoutes...';


GO
ALTER TABLE [dbo].[RoutePlan] WITH NOCHECK
    ADD CONSTRAINT [ForeignKeyConstraintRoutePlanRoutes] FOREIGN KEY ([id_route]) REFERENCES [dbo].[Routes] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
/*
Шаблон скрипта после развертывания							
--------------------------------------------------------------------------------------
 В данном файле содержатся операторы SQL, которые будут добавлены в скрипт построения.		
 Используйте синтаксис SQLCMD для включения файла в скрипт после развертывания.			
 Пример:      :r .\myfile.sql								
 Используйте синтаксис SQLCMD для создания ссылки на переменную в скрипте после развертывания.		
 Пример:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO
PRINT N'Существующие данные проверяются относительно вновь созданных ограничений';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [dbo].[RouteList] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRouteListDriver];

ALTER TABLE [dbo].[RouteList] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRouteListRouteTable];

ALTER TABLE [dbo].[RouteList] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRouteListVehicle];

ALTER TABLE [dbo].[RouteList] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRouteListPersonDispetcher];

ALTER TABLE [dbo].[RouteList] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRouteListPersonMechanic];

ALTER TABLE [dbo].[VokzalTable] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintVokzalTableRouteList];

ALTER TABLE [dbo].[RouteListInfo] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRouteListInfoRouteList];

ALTER TABLE [dbo].[RouteListSchedule] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRouteListScheduleRouteList];

ALTER TABLE [dbo].[Cash] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintCashRouteList];

ALTER TABLE [dbo].[RoutePlan] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRoutePlanRouteTable];

ALTER TABLE [dbo].[RoutePlan] WITH CHECK CHECK CONSTRAINT [ForeignKeyConstraintRoutePlanRoutes];


GO
